package com.persist.core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class JDBCUtils {
	private static final Logger logger = LoggerFactory.getLogger(JDBCUtils.class);
	private static String connect;
	private static String driverClassName;
	private static String URL;
	private static String userName;
	private static String password;
	private static boolean autoCommit;

	private static Connection conn;

	static {
		config();
	}

	private static void config() {
		driverClassName = "com.mysql.jdbc.Driver";
		URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
		userName = "root";
		password = "mysql";
		autoCommit = false;
	}

	private static boolean load() {
		try {
			Class.forName(driverClassName);
			return true;
		} catch (ClassNotFoundException e) {
			logger.error("Loading driver class has error.", e);
		}
		return false;
	}

	private static boolean invalid() {
		if (conn != null) {
			try {
				// Determine whether the connection is valid.
				// If connection isn't closed or connection is valid, return true
				if (conn.isClosed() || !conn.isValid(3)) {
					return true;
				}
			} catch (SQLException e) {
				logger.error("Checking status of connection has error.", e);
			}
			return false;
		} else {
			return true;
		}
	}

	public static Connection connect() {
		if (invalid()) {
			boolean loadFlag = load();
			if (loadFlag) {
				try {
					conn = DriverManager.getConnection(URL, userName, password);
				} catch (SQLException e) {
					logger.error("Creating connection has error.", e);
				}
			} else {
				logger.error("Loading driver class fail.");
			}
		}
		return conn;
	}

	public static void transaction() {
		try {
			conn.setAutoCommit(autoCommit);
		} catch (SQLException e) {
			logger.error("Setting the mode of auto commit has error.", e);
		}
	}

	public static Statement statement() {
		Statement st = null;
		Connection conn = connect();
		if (conn != null) {
			try {
				transaction();
				st = conn.createStatement();
			} catch (SQLException e) {
				logger.error("Creating statement has error.", e);
			}
		}
		return st;
	}

	public static PreparedStatement prepare(String sql, boolean autoGeneratedKeys) {
		PreparedStatement ps = null;
		Connection conn = connect();
		if (conn != null) {
			try {
				transaction();
				if (autoGeneratedKeys) {
					ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				} else {
					ps = conn.prepareStatement(sql);
				}
			} catch (SQLException e) {
				logger.error("Creating prepare statement has error.", e);
			}
		}
		return ps;
	}

	public static ResultSet query(String sql, List<Object> params) {
		if (sql == null || sql.trim().isEmpty() || sql.trim().toLowerCase().startsWith("select")) {
			throw new RuntimeException("The syntax of query  is illegal.");
		}
		ResultSet rs = null;
		if (params.size() > 0) {
			PreparedStatement ps = prepare(sql, false);

			if (ps == null) {
				return rs;
			}

			try {
				for (int i = 0; i < params.size(); i++) {
					ps.setObject(i + 1, params.get(i));
				}
				rs = ps.executeQuery();
			} catch (SQLException e) {
				logger.error("Executing query prepared statement has error.", e);
			}
		} else {
			Statement st = statement();

			if (st == null) {
				return rs;
			}

			try {
				rs = st.executeQuery(sql);
			} catch (SQLException e) {
				logger.error("Executing query statement has error.", e);
			}
		}
		return rs;
	}

	private static Object typeof(Object obj) {
		Object r = obj;
		if (obj instanceof java.sql.Timestamp) {
			return r;
		}

		if (obj instanceof Date) {
			java.util.Date d = (java.util.Date) obj;
			r = new java.sql.Date(d.getTime());
			return r;
		}

		if (obj instanceof Character || obj.getClass() == char.class) {
			r = String.valueOf(obj);
			return r;
		}

		return r;
	}

	public static boolean execute(String sql, Object... params) {
		if (sql == null || sql.trim().isEmpty() || sql.trim().toLowerCase().startsWith("select")) {
			throw new RuntimeException("The syntax of execute  is illegal.");
		}
		boolean r = false;
		sql = sql.trim();
		sql = sql.toLowerCase();
		String prefix = sql.substring(0, sql.indexOf(" "));
		String operation = "";

		switch (prefix) {
		case "create":
			operation = "create table";
			break;
		case "alter":
			operation = "update table";
			break;
		case "drop":
			operation = "drop table";
			break;
		case "truncate":
			operation = "truncate table";
			break;
		case "insert":
			operation = "insert :";
			break;
		case "update":
			operation = "update :";
			break;
		case "delete":
			operation = "delete :";
			break;
		}

		if (params.length > 0) {
			PreparedStatement ps = prepare(sql, false);

			if (ps == null) {
				return r;
			}

			Connection conn = null;
			try {
				conn = ps.getConnection();
			} catch (SQLException e) {
				logger.error("Get connection from prepared statement has error.", e);
			}

			if (conn != null) {
				try {
					for (int i = 0; i < params.length; i++) {
						Object p = typeof(params[i]);
						ps.setObject(i + 1, p);
					}
					ps.executeUpdate();
					commit(conn);
					r = true;
				} catch (SQLException e) {
					logger.error("Execute sql from prepared statement has error.", e);
					rollback(conn);
				}
			}
		} else {
			Statement st = statement();

			if (st == null) {
				return r;
			}

			Connection conn = null;
			try {
				conn = st.getConnection();
			} catch (SQLException e) {
				logger.error("Get connection from statement has error.", e);
			}

			if (conn != null) {
				try {
					st.executeUpdate(sql);
					commit(conn);
					r = true;
				} catch (SQLException e) {
					logger.error("Execute sql from statement has error.", e);
					rollback(conn);
				}
			}
		}
		return r;
	}

	public static int insert(String sql, boolean autoGeneratedKeys, List<Object> params) {
		int var = -1;
		if (sql == null || sql.trim().isEmpty()) {
			throw new RuntimeException("No sql will be execute.");
		}
		if (!sql.trim().toLowerCase().startsWith("insert")) {
			throw new RuntimeException("The syntax of insert  is illegal. ");
		}
		// 获得 被执行的 SQL 语句的 前缀 ( 第一个单词 )
		sql = sql.trim();
		sql = sql.toLowerCase();

		if (params.size() > 0) {
			PreparedStatement ps = prepare(sql, false);

			if (ps == null) {
				return var;
			}

			Connection conn = null;
			try {
				conn = ps.getConnection();
			} catch (SQLException e) {
				logger.error("Get connection from prepared statement has error.", e);
			}

			if (conn != null) {
				try {
					for (int i = 0; i < params.size(); i++) {
						Object p = typeof(params.get(i));
						ps.setObject(i + 1, p);
					}
					int count = ps.executeUpdate();
					if (autoGeneratedKeys) {
						ResultSet rs = ps.getGeneratedKeys();
						if (rs.next()) {
							var = rs.getInt(1);
						} else {
							var = count;
						}
					}
					commit(conn);
				} catch (SQLException e) {
					logger.error("Execute sql from prepared statement has error.", e);
					rollback(conn);
				}
			}
		} else {
			Statement st = statement();

			if (st == null) {
				return var;
			}

			Connection conn = null;
			try {
				conn = st.getConnection();
			} catch (SQLException e) {
				logger.error("Get connection from statement has error.", e);
			}

			if (conn != null) {
				try {
					int count = st.executeUpdate(sql);
					if (autoGeneratedKeys) {
						ResultSet rs = st.getGeneratedKeys();
						if (rs.next()) {
							var = rs.getInt(1);
						} else {
							var = count;
						}
					}
					commit(conn);
				} catch (SQLException e) {
					logger.error("Execute sql from statement has error.", e);
					rollback(conn);
				}
			}
		}
		return var;
	}

	private static void commit(Connection c) {
		if (c != null && !autoCommit) {
			try {
				c.commit();
			} catch (SQLException e) {
				logger.error("Commit transaction has error.", e);
			}
		}
	}

	private static void rollback(Connection c) {
		if (c != null && !autoCommit) {
			try {
				c.rollback();
			} catch (SQLException e) {
				logger.error("Rollback transaction has error.", e);

			}
		}
	}

	public static void release(Object closeable) {
		if (closeable != null) {
			if (closeable instanceof ResultSet) {
				ResultSet rs = (ResultSet) closeable;
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error("Close resultset has error.", e);
				}
			}

			if (closeable instanceof Statement) {
				Statement st = (Statement) closeable;
				try {
					st.close();
				} catch (SQLException e) {
					logger.error("Close statement has error.", e);
				}
			}

			if (closeable instanceof PreparedStatement) {
				PreparedStatement ps = (PreparedStatement) closeable;
				try {
					ps.close();
				} catch (SQLException e) {
					logger.error("Close prepared statement has error.", e);
				}
			}

			if (closeable instanceof Connection) {
				Connection c = (Connection) closeable;
				try {
					c.close();
				} catch (SQLException e) {
					logger.error("Close connection has error.", e);
				}
			}
		}
	}
}
